꿈꾸는 개발자, DBA 커뮤니티 구루비

조건
1. 대상 : coh_no=030의 que_form_no의 응답 개수(answer)와 gum=2 개수

메인 화면

설문지

쿼리 및 트레이스
{code:SQL}
1. Query

select que_form_no, sum(answer), sum(gum)
from (select que_form_no, count(answer) answer, to_number('0') gum
from research
where coh_no='030' group by que_form_no
union all
select que_form_no, to_number('0') answer, count(gum) gum
from gumsu
where coh_no='030' and gum='2' and gum_date between '20090101' and '20090130'
group by que_form_no, to_char(0,'99'))
group by que_form_no
order by que_form_no;

{code:SQL}
2. Result

QUE_FORM_NO          SUM(ANSWER)   SUM(GUM)
-------------------- ----------- ----------
1                           1322         35
2                           5726         35
3                            110         36
4                            110         35
5                            517         35
6                           1419         35
7                           1410         35
8                            762         35
9                           1152         35
10                           190         35
11                           497         36
12                           620         35
13                          1289         35
14                          1862         27
15                          2289         35
16                           538         35
17                           576         35
18                         18898         35

18 rows selected.


3. Execution Plan

Execution Plan
----------------------------------------------------------
Plan hash value: 739072605

--------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows  | Bytes | Cost(%CPU)| Time      |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |    35 |  1155 | 38976   (1)| 00:07:48 |
|   1 |  SORT GROUP BY                  |                |    35 |  1155 | 38976   (1)| 00:07:48 |
|   2 |   VIEW                          |                |    35 |  1155 | 38975   (1)| 00:07:48 |
|   3 |    UNION-ALL                    |                |       |       |            |          |
|   4 |     SORT GROUP BY NOSORT        |                |    34 |   340 | 38297   (1)| 00:07:40 |
|   5 |      TABLE ACCESS BY INDEX ROWID| RESEARCH       | 38392 |   374K| 38297   (1)| 00:07:40 |
|*  6 |       INDEX RANGE SCAN          | INDEX_RESEARCH | 38392 |       |   243   (1)| 00:00:03 |
|   7 |     HASH GROUP BY               |                |     1 |    15 |   677   (1)| 00:00:09 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| GUMSU          |     1 |    15 |   676   (0)| 00:00:09 |
|*  9 |       INDEX RANGE SCAN          | GUMSU_S_PK     |  1950 |       |    19   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("COH_NO"='030')
       filter("COH_NO"='030')
   8 - filter("GUM_DATE">=20090101 AND "GUM"='2' AND "GUM_DATE"<=20090130)
   9 - access("COH_NO"='030')

함수의 함정
1. gumsu 테이블의 group by에서 to_char(0,'99')대신 {*}to_number('0')*를 사용
2. 실행계획은 동일하나, index range scan후 {*}to_number('0')* 때문에 filter 작업이 추가되었음
3. 위 쿼리에서는 to_char(0,'99')로 되어 있어서 filter작업을 수행하지 않음
4. 에러가 발생할 줄 알았는데 결과값이 나왔으며 실행계획까지 동일하게 나와서 나름 어이없는 속임수에 넘어간 느낌이 들어 써봤음~~^^;;


1. Query

select que_form_no, sum(answer), sum(gum)
from (select que_form_no, count(answer) answer, to_number('0') gum
      from research
      where coh_no='030' group by que_form_no
      union all
      select que_form_no, to_number('0') answer, count(gum) gum
      from gumsu
      where coh_no='030' and gum='2' and gum_date between '20090101' and '20090130'
      group by que_form_no, to_number('0'))
group by que_form_no
order by que_form_no;


Execution Plan
----------------------------------------------------------
Plan hash value: 2616170847
--------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows  | Bytes | Cost(%CPU)| Time      |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |    35 |  1155 | 38975   (1)| 00:07:48 |
|   1 |  SORT GROUP BY                  |                |    35 |  1155 | 38975   (1)| 00:07:48 |
|   2 |   VIEW                          |                |    35 |  1155 | 38974   (1)| 00:07:48 |
|   3 |    UNION-ALL                    |                |       |       |            |          |
|   4 |     SORT GROUP BY NOSORT        |                |    34 |   340 | 38297   (1)| 00:07:40 |
|   5 |      TABLE ACCESS BY INDEX ROWID| RESEARCH       | 38392 |   374K| 38297   (1)| 00:07:40 |
|*  6 |       INDEX RANGE SCAN          | INDEX_RESEARCH | 38392 |       |   243   (1)| 00:00:03 |
|   7 |     SORT GROUP BY NOSORT        |                |     1 |    15 |   676   (0)| 00:00:09 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| GUMSU          |     1 |    15 |   676   (0)| 00:00:09 |
|*  9 |       INDEX RANGE SCAN          | GUMSU_S_PK     |  1950 |       |    19   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("COH_NO"='030')
       filter("COH_NO"='030')
   8 - filter("GUM_DATE">=20090101 AND "GUM"='2' AND "GUM_DATE"<=20090130)
   9 - access("COH_NO"='030')
       filter("COH_NO"='030')

COH System (by joang77) [2009.05.18]